﻿using EfCoreQuery.Entity;
using Microsoft.EntityFrameworkCore;
using Microsoft.IdentityModel.Tokens;

namespace EfCoreQuery
{
    internal class Program
    {
        static void Main(string[] args)
        {
           using(var db = new SqlDbContext())
            {
                //var st1 = new Student() { Name = "Tom" };
                //var st2 = new Student() { Name = "jerry" };
                //var st3 = new Student() { Name = "lily" };
                //var ts1 = new Teacher() { Name ="Miss wang", Address="Zhengzhou", Age =18, JobYear =2, Students = new List<Student>() { st1, st2, st3 } };
                //var ts2 = new Teacher() { Name = "Miss Li", Address = "JinShui", Age = 22, JobYear = 3, Students = new List<Student>() { st1, st2 } };
                //db.Students.Add(st1);
                //db.Students.Add(st2);
                //db.Students.Add(st3);
                //db.Teachers.Add(ts1);
                //db.Teachers.Add(ts2);
                //db.SaveChanges();

                // 直接传委托是全部查询，然后在内存中过滤
                //Func<Teacher, bool> func = p => p.Age == 18;
                //var teachers = db.Teachers.Include(p => p.Students).Where(func); where方法引用的是 IEnumable 的扩展方法
                //var teachers = db.Teachers.Include(p=>p.Students).Where(p=>p.Age == 18); 测试的Where 是IQueryable 的扩展方法
                //foreach(var item  in teachers)
                //{
                //    Console.WriteLine($"Teacher:{item.Name}-student:{string.Join(',',item.Students.Select(p=>p.Name))}");
                //}
                // IQueryable 复用
                //var iqueryable = db.Teachers.Where(p => p.JobYear > 1);
                //Console.WriteLine($"count:{iqueryable.Count()}");
                //Console.WriteLine($"maxJobYear:{iqueryable.Max(p=>p.JobYear)}");

                ////  分页
                //var pageList = PageList(3,2);
                //foreach (var item in pageList)
                //{
                //    Console.WriteLine($"Id:{item.Id},Teacher:{item.Name}-student:{string.Join(',', item.Students.Select(p => p.Name))}");
                //}

                // 执行非查询sql 语句
                //var name = "Mirss Gao";
                //var jobYear = 5;
                //var age = 30;
                //var address = "Zheng zhou Area";
                //var rows = db.Database.ExecuteSqlInterpolated($@"insert into T_Teacher(Name,jobYear,Age,Address)
                //            values({name},{jobYear},{age},{address})");

                //var newName = "Missis Qiao";
                //var id = 12;
                //var updateRows = db.Database.ExecuteSqlInterpolated($"update T_Teacher set name={newName} where id={id}");

                // 随机排序
                //var name = "%wang%";
                //var rows = db.Teachers.FromSqlInterpolated($"select * from T_Teacher where name like {name} order by newid() desc");
                //foreach (var item in rows)
                //{
                //    Console.WriteLine($"{item.Name},{item.Id}");
                //    Thread.Sleep(500);
                //}

                // 查看实体状态
                //var rows = db.Teachers.FromSqlInterpolated($"select top 3 *  from T_Teacher order by id asc").ToArray();
                //var t1 = rows[0];
                //var t2 = rows[1];
                //var t3 = rows[2];
                //var t4 = new Teacher() { Name = "Teacher4", Address = "JinShuiArea", Age = 18, JobYear = 16 };
                //var t5 = new Teacher() { Name = "Teacher5", Address = "HuiJiArea", Age = 19, JobYear = 17 };
                //t1.Name = "Teachear1";
                //db.Remove(t2);
                //db.Add(t4);
                //var entry1 = db.Entry(t1);
                //var entry2 = db.Entry(t2);
                //var entry3 = db.Entry(t3);
                //var entry4 = db.Entry(t4);
                //var entry5 = db.Entry(t5);
                //Console.WriteLine($"t1.state:{entry1.State}");
                //Console.WriteLine($"t1.debugView:{entry1.DebugView.LongView}");
                //Console.WriteLine($"t2.state:{entry2.State}");
                //Console.WriteLine($"t3.state:{entry3.State}");
                //Console.WriteLine($"t4.state:{entry4.State}");
                //Console.WriteLine($"t5.state:{entry5.State}");

                // asNoTracking
                //var rows = db.Teachers.AsNoTracking().Take(1).ToList();
                //var t1 = rows[0];
                //t1.Name = "A";
                //var entry1= db.Entry(t1);
                //Console.WriteLine(entry1.State);

                var t1 = new Teacher() { Id = 1 };
                t1.Name = "abc";
                var entry = db.Entry(t1);
                entry.Property("Name").IsModified = true;
                Console.WriteLine(entry.DebugView.LongView);
                db.SaveChanges();

                Console.ReadKey();
            }

           // 分页
            static IEnumerable<Teacher> PageList(int pageIndex, int pageSize)
            {
                using(var db = new SqlDbContext()) {

                  return   db.Teachers.Include(p=>p.Students).Skip((pageIndex-1) * pageSize).Take(pageSize).ToList();
                }
            }
        }
    }
}